package co.th.helper.dao;

import java.math.BigDecimal;
import java.util.ArrayList;
import java.util.Calendar;
import java.util.List;

import org.apache.log4j.Logger;

import co.th.helper.common.CommonUtils;
import co.th.helper.jdbc.JdbcTemplate;
import co.th.helper.jdbc.mapper.PersonRowMapper;
import co.th.helper.model.Person;

public class PersonDAO extends JdbcTemplate<Person>{
	
	private static Logger _logger = Logger.getLogger(PersonDAO.class);

	public List<Person> findAll() throws Exception{
		try {
			return findByCriteria(null);
		} catch (Exception ex) {
			_logger.error("Exception : " + ex.getMessage());
			throw new RuntimeException("Can't get all person");
		}
	}
	
	public Person findByPK(BigDecimal person_code) throws Exception{
		try {
			Person person = new Person();
			person.setPersonCode(person_code);
			List<Person> list = findByCriteria(person);
			if (null == list || list.isEmpty()){
				throw new Exception("Record not found.");
			}
			return list.get(0);
		} catch (Exception e) {
			throw new RuntimeException("Cat not get all person");
		}
	}
	
	public List<Person> findByCriteria(Person person) throws Exception{
		try {
			StringBuffer sql = new StringBuffer("select * from person where 1=1 ");
			List<Object> paramObj = new ArrayList<Object>(0);
			
			if (null != person){
				int index = 0;
				if (person.getPersonCode() != null && person.getPersonCode().intValue() > 0){
					sql.append(" and person_code = ? ");
					paramObj.add(index++, person.getPersonCode());
				}
				if (!CommonUtils.chkIsnull(person.getPersonTitle())){
					sql.append(" and person_title = ? ");
					paramObj.add(index++, person.getPersonTitle());
				}
				if (!CommonUtils.chkIsnull(person.getPersonFirstName())){
					sql.append(" and person_first_name = ? ");
					paramObj.add(index++, person.getPersonFirstName());
				}
				if (!CommonUtils.chkIsnull(person.getPersonLastName())){
					sql.append(" and person_last_name = ? ");
					paramObj.add(index++, person.getPersonLastName());
				}
				if (null != person.getPersonType()){
					sql.append(" and person_type = ? ");
					paramObj.add(index++, person.getPersonType());
				}
				if (!CommonUtils.chkIsnull(person.getTelephone())){
					sql.append(" and telephone = ? ");
					paramObj.add(index++, person.getTelephone());
				}
				if (!CommonUtils.chkIsnull(person.getEmail())){
					sql.append(" and email = ? ");
					paramObj.add(index++, person.getEmail());
				}
				if (null != person.getImp() && person.getImp().intValue() > 0){
					sql.append(" and imp_code = ? ");
					paramObj.add(index++, person.getImp());
				}
				if (null != person.getRp() && person.getRp().intValue() > 0){
					sql.append(" and rp_code = ? ");
					paramObj.add(index++, person.getRp());
				}
				if (!CommonUtils.chkIsnull(person.getRemark())){
					sql.append(" and remark = ? ");
					paramObj.add(index++, person.getRemark());
				}	
			}
			return this.queryList(sql.toString(), CommonUtils.convListToArray(paramObj), new PersonRowMapper());
		} catch (Exception e) {
			throw new RuntimeException("Can't get person");
		}
	}
	
	public List<Person> save(List<Person> listPerson) throws Exception{
		for(Person person : listPerson){
			this.save(person);
		}
		return listPerson;
	}
	
	public synchronized Person save(Person person) throws Exception{
		try {
			person.setPersonCode(genPersonCode());
			
			final String sql = "INSERT INTO person (person_code,person_title,person_first_name," +
					"person_last_name,person_type,telephone,email,imp_code,rp_code,remark,create_by," +
					"create_date,update_by,update_date) " +
					" VALUES ( ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);";
			Object[] params = new Object[14];
			params[0] = person.getPersonCode();
			params[1] = person.getPersonTitle();
			params[2] = person.getPersonFirstName();
			params[3] = person.getPersonLastName();
			params[4] = person.getPersonType();
			params[5] = person.getTelephone();
			params[6] = person.getEmail();
			params[7] = person.getImp();
			params[8] = person.getRp();
			params[9] = person.getRemark();
			params[10] = person.getCreateBy();
			params[11] = person.getCreateDate();
			params[12] = person.getUpdateBy();
			params[13] = person.getUpdateDate();
			
			int rowOfProcess = this.saveUnique(sql, params);
			if (rowOfProcess < 1){
				throw new Exception("Can't save person.");
			}
			return person;
		} catch (Exception ex) {
			throw ex;
		}
	}
	
	public int update(Person person) throws Exception{
		try {
			StringBuffer sql = new StringBuffer("UPDATE person SET ");
			List<Object> paramObj = new ArrayList<Object>(0);
			
			if (person.getPersonCode() == null || person.getPersonCode().intValue() <= 0){
				throw new Exception("No primary key.");
			}
			
			int index = 0;
			sql.append(" update_date = ?  ");
			paramObj.add(index++, new java.sql.Date(Calendar.getInstance().getTime().getTime()));
			if (!CommonUtils.chkIsnull(person.getPersonTitle())){
				sql.append(" , person_title = ? ");
				paramObj.add(index++, person.getPersonTitle());
			}
			if (!CommonUtils.chkIsnull(person.getPersonFirstName())){
				sql.append(" , person_first_name = ? ");
				paramObj.add(index++, person.getPersonFirstName());
			}
			if (!CommonUtils.chkIsnull(person.getPersonLastName())){
				sql.append(" , person_last_name = ? ");
				paramObj.add(index++, person.getPersonLastName());
			}
			if (null != person.getPersonType()){
				sql.append(" , person_type = ? ");
				paramObj.add(index++, person.getPersonType());
			}
			if (!CommonUtils.chkIsnull(person.getTelephone())){
				sql.append(" , telephone = ? ");
				paramObj.add(index++, person.getTelephone());
			}
			if (!CommonUtils.chkIsnull(person.getEmail())){
				sql.append(" , email = ? ");
				paramObj.add(index++, person.getEmail());
			}
			if (null != person.getImp() && person.getImp().intValue() > 0){
				sql.append(" , imp_code = ? ");
				paramObj.add(index++, person.getImp());
			}
			if (null != person.getRp() && person.getRp().intValue() > 0){
				sql.append(" , rp_code = ? ");
				paramObj.add(index++, person.getRp());
			}
			if (!CommonUtils.chkIsnull(person.getRemark())){
				sql.append(" , remark = ? ");
				paramObj.add(index++, person.getRemark());
			}
			sql.append(" WHERE person_code = ? ");
			paramObj.add(index++, person.getPersonCode());
			
			return updateUnique(sql.toString(), CommonUtils.convListToArray(paramObj), new PersonRowMapper());
		} catch (Exception ex) {
			throw new RuntimeException("Can't update person");
		}
	}
	
	public int delete(BigDecimal personCode) throws Exception{
		try {		
			if (personCode == null || personCode.intValue() <= 0){
				throw new Exception("No primary key.");
			}
			
			final String sql = "DELETE FROM person WHERE person_code = ?";
			Object[] param = new Object[]{personCode};
			
			return updateUnique(sql.toString(), param, new PersonRowMapper());
		} catch (Exception ex) {
			throw new RuntimeException("Can't delete person");
		}
	}
	
	private BigDecimal genPersonCode() throws Exception{
		final String sql = "select max(person_code) AS person_code from person";
		Person objRow = queryUnique(sql, null, new PersonRowMapper());
		return BigDecimal.valueOf(objRow.getPersonCode().intValue() + 1);
	}
}
